package com.module.ble.db

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.RawQuery
import androidx.room.Update
import androidx.sqlite.db.SupportSQLiteQuery
import com.module.ble.db.table.UserStress
import kotlinx.coroutines.flow.Flow

/**
 * 作者：sosou
 * <p>
 * 版本：1.0
 * <p>
 * 创建日期：2025/08/19
 * <p>
 * 描述：
 * <p>
 * 修订历史：
 */
@Dao
interface UserStressDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(vararg bean: UserStress)

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(mList:List<UserStress>)

    @Query("SELECT createdTime FROM user_stress WHERE createdTime IN (:times)")
    fun getExistingTimes(times: List<Long>): List<Long>

    @Query("SELECT * FROM user_stress")
    fun findAll(): MutableList<UserStress>

    @Query("SELECT * FROM user_stress WHERE sysStat = 0")
    fun findAllNotUp(): MutableList<UserStress>

    @Query("SELECT * FROM user_stress")
    fun findAllFlow(): Flow<List<UserStress>>

    // 先获取最新日期，再查询该日期的所有数据
    @Query("SELECT * FROM user_stress WHERE date(createdTime/1000, 'unixepoch', 'localtime') = (SELECT date(MAX(createdTime)/1000, 'unixepoch', 'localtime') FROM user_stress) ORDER BY createdTime DESC")
    fun findLatestDayRecords(): List<UserStress>

    @Query("SELECT * FROM user_stress WHERE date(createdTime/1000, 'unixepoch', 'localtime') = (SELECT date(MAX(createdTime)/1000, 'unixepoch', 'localtime') FROM user_stress) ORDER BY createdTime DESC")
    fun findLatestDayFlow(): Flow<List<UserStress>>

    //根据时间范围查询数据
    @Query("SELECT * FROM user_stress WHERE createdTime >= :startTime AND createdTime <= :endTime ORDER BY createdTime ASC")
    fun findByTimeRange(startTime: Long, endTime: Long): List<UserStress>

    // 根据时间范围查询数据（Flow版本）
    @Query("SELECT * FROM user_stress WHERE createdTime >= :startTime AND createdTime <= :endTime ORDER BY createdTime ASC")
    fun findByTimeRangeFlow(startTime: Long, endTime: Long): Flow<List<UserStress>>

    // 查询 所有  倒序 查询
    @Query("SELECT * FROM user_stress ORDER BY createdTime DESC  LIMIT :mSize")
    fun findByDescSize(mSize: Int): MutableList<UserStress>

    // 根据指定时间戳查询当天及前29天的所有数据（共30天）
    @Query("""SELECT * FROM user_stress 
             WHERE date(createdTime/1000, 'unixepoch', 'localtime') 
             BETWEEN date(:timestamp/1000, 'unixepoch', 'localtime', '-29 days') 
             AND date(:timestamp/1000, 'unixepoch', 'localtime') 
             ORDER BY createdTime ASC""")
    fun findLast30DaysFromTimestamp(timestamp: Long): List<UserStress>

    // 根据指定时间戳查询当天及前29天的所有数据（Flow版本）
    @Query("""SELECT * FROM user_stress 
             WHERE date(createdTime/1000, 'unixepoch', 'localtime') 
             BETWEEN date(:timestamp/1000, 'unixepoch', 'localtime', '-29 days') 
             AND date(:timestamp/1000, 'unixepoch', 'localtime') 
             ORDER BY createdTime ASC""")
    fun findLast30DaysFromTimestampFlow(timestamp: Long): Flow<List<UserStress>>

    /**
     * 根据指定时间戳查询该时间戳当天内的所有数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 当天内的所有数据列表
     */
    @Query("""SELECT * FROM user_stress 
             WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') 
             ORDER BY createdTime ASC""")
    fun findByTimestampTheDay(timestamp: Long): List<UserStress>

    /**
     * 根据指定时间戳查询该时间戳当天内的所有数据（Flow版本）
     * @param timestamp 指定的时间戳（毫秒）
     * @return Flow<List<UserStress>>
     */
    @Query("""SELECT * FROM user_stress 
             WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') 
             ORDER BY createdTime ASC""")
    fun findByTimestampTheDayFlow(timestamp: Long): Flow<List<UserStress>>

    /**
     * 根据传入时间戳查询该时间戳当天内最新的那条数据
     * @param timestamp 指定的时间戳（毫秒）
     * @return 当天最新的数据，如果没有数据则返回null
     */
    @Query("SELECT * FROM user_stress WHERE date(createdTime/1000, 'unixepoch', 'localtime') = date(:timestamp/1000, 'unixepoch', 'localtime') ORDER BY createdTime DESC LIMIT 1")
    fun findLatestByDay(timestamp: Long): UserStress?

    @RawQuery
    fun findByCustomQuery(query: SupportSQLiteQuery): List<UserStress>

    @Update
    fun update(msg: UserStress)

    @Update
    fun update(mList: List<UserStress>)

    @Query("select count(*) from user_stress")
    fun count(): Int

    //查询某条数据
    @Query("SELECT * FROM user_stress WHERE createdTime = :time")
    fun findMessageByTime(time:Long): UserStress

    //删除所有 → '所有'的只能用@Query,@Query可以编写任意的sql语句
    @Query("DELETE FROM user_stress")
    fun deleteAll()

    @Query("DELETE FROM user_stress WHERE createdTime = :time")
    fun deleteMsgByTime(time:Long)

}